In typical database systems, users store, update and retrieve information by submitting commands to a database application, such as Oracle. When executing transactions, the database application stores information in memory and on disk. For best performance, as much information as possible must be stored in memory rather than on disk. However, as memory resources are limited, the database application must tune memory allocation, which involves distributing available memory to structures used by the database application.
As described in a book entitled “Oracle8i Concepts” available from Oracle Corporation, and on the Internet at an address obtained by replacing “#” with “.” and “$” with “/” in the following string “$$oradoc#photo#net$ora81$DOC$server#815$a67781$toc#htm” (which book is incorporated by reference herein in its entirety), two of the structures used by the database application Oracle are memory areas called System Global Area (SGA) and Program Global Area (PGA). The SGA contains general information about the state of the database and the instance, which the Oracle processes need to access. No user data is stored in the SGA. The size of the SGA is determined at start up of Oracle. For optimal performance in most systems, the entire SGA should fit in real memory. A database administrator (DBA) can see how much memory is allocated to the SGA and each of its internal structures by issuing the SQL statement “SHOW SGA.”
A PGA is created for holding data and control information of each process, when the process is started. The PGA is private to each process in Oracle, although such PGA can be in shared memory. A PGA's initial size is fixed at startup of the corresponding process, and is operating-system specific. Currently, in Oracle8i, the DBA can control the PGA memory utilization, using various parameters like SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE. For more information on such parameters, see the book entitled “Oracle8i Tuning” available at an address obtained by replacing “#” with “.” and “$” with “/” in the following string $$oradoc#photo#net$ora81$DOC$server#815$a67775$toc#htm (which book is incorporated by reference herein in its entirety).
See also U.S. Pat. No. 5,799,210 granted to Cohen, et al. entitled “Method for allocating either private or shared buffer memory for storing data from sort operations in accordance with an assigned value or threshold value”, U.S. Pat. No. 5,987,580 Jasuja, et al. entitled “Serially reusable execution memory”, U.S. Pat. No. 5,860,144 Frank, et al entitled “Addressing method and system for providing access of a very large size physical memory buffer to a number of processes”, and U.S. Pat. No. 5,784,699 McMahon, et al. “Dynamic memory allocation in a computer using a bit map index” all of which are related to the use of memory by database processes.
For additional information, see the paper by Luc Bouganim, Olga Kapitskaia, and Patrick Valduriez, entitled “Dynamic Memory Allocation for Large Query Execution” published in Networking and Information Systems 1(6): 629-652 (1998). See also the paper entitled, “Memory-Adaptive Scheduling for Large Query Execution” by these same three authors, pages 105-115 of Proceedings of Conference on Information and Knowledge Management, 1998 published by Association for Computing Machinery, Inc. Yet another paper in this field is by Diane L. Davison and Goetz Graefe, entitled “Dynamic Resource Brokering for Multi-User Query Execution”, published in SIGMOD Conference 1995: 281-292.